package db.tables.reference;

import java.sql.SQLException;
import java.util.ArrayList;

import org.hsqldb.Types;

import db.DBObject;
import db.tables.DBSchoolTable;
import db.tables.DBUserTable;

public class DBScholarAtTable extends DBObject {
	public static final String TABLE_NAME = "scholar_at";

	public static final String SCHOLAR_AT_ID_COL = TABLE_NAME + "_id",
			SCHOOL_ID_COL = DBSchoolTable.SCHOOL_ID_COL,
			USER_ID_COL = DBUserTable.USER_ID_COL,
			START_DATE_COL = "start_date", END_DATE_COL = "end_date";

	public DBScholarAtTable() throws ClassNotFoundException, SQLException {
		super(TABLE_NAME, new Object[][] {
				{ SCHOLAR_AT_ID_COL, Types.BIGINT, SQL_IDENTITY_PRIMARY_KEY,"" },
				{ SCHOOL_ID_COL, Types.BIGINT, SQL_NOT_NULL, "" },
				{ USER_ID_COL, Types.BIGINT, SQL_NOT_NULL, "" },
				{ START_DATE_COL, Types.DATE, SQL_NOT_NULL, "" },
				{ END_DATE_COL, Types.DATE, getSQLCheckEndDate()
						+getSQLForeignKeyReference(DBSchoolTable.TABLE_NAME,SCHOOL_ID_COL)
						+getSQLForeignKeyReference(DBUserTable.TABLE_NAME,USER_ID_COL),"" } });
	}
	
	public ArrayList<ArrayList<Object>> getArrayListByUserID(Long user_id){
		return getReferenceTableJoin(DBScholarAtTable.TABLE_NAME, DBSchoolTable.TABLE_NAME, 
				getAbsoluteColumn(SCHOOL_ID_COL), 
				getAbsoluteColumn(DBSchoolTable.TABLE_NAME, DBSchoolTable.SCHOOL_ID_COL), 
				getAbsoluteColumn(USER_ID_COL),user_id);
	}
}
